package com.autocloud.filter.service;

import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zhongwb
 * @version 1.0
 * @description 动态脚本辅助类
 * @date 2019/12/3 8:40 下午
 */
@Component
public class SqlExcuteHelper {

    @Autowired
    private EntityManager entityManager;

    /**
     * 抛出异常,所有操作将回滚
     *
     * @param message
     * @throws Exception
     */
    public void Exception(String message) throws Exception {
        throw new Exception(message);
    }

    /**
     * 执行一段SQL,如update,修改表结构
     *
     * @param sql
     * @param param
     * @throws Exception
     */
    public int executeSql(String sql, Map<String, Object> param) throws Exception {
        Query query = entityManager.createNativeQuery(sql);
        param.forEach((key, value) -> query.setParameter(key, value));
        return query.executeUpdate();
    }

    /**
     * 查询SQL结果
     *
     * @param sql
     * @param param
     * @return
     * @throws Exception
     */
    public Map<String, Object> queryMap(String sql, Map<String, Object> param) throws Exception {
        Query query = entityManager.createNativeQuery(sql);
        param.forEach((key, value) -> query.setParameter(key, value));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map<String, Object> result = new HashMap<>();
        if (query.getMaxResults() == 0) {
            return result;
        }
        try {
            result = (Map<String, Object>) query.setMaxResults(1).getSingleResult();
        } catch (NoResultException ex) {
            return result;
        }
        return result;
    }

    /**
     * 获取JPA的EntityManager  对象
     *
     * @return
     */
    public EntityManager getEntityManager() {
        return entityManager;
    }

    /**
     * 查询SQL结果集
     *
     * @param sql
     * @param param
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryList(String sql, Map<String, Object> param) throws Exception {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        param.forEach((key, value) -> query.setParameter(key, value));
        List<Map<String, Object>> result = query.getResultList();
        return result;
    }

    /**
     * 查询分页结果
     *
     * @param sql
     * @param param
     * @param page
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryPage(String sql, Map<String, Object> param, int page) throws Exception {
        Query query = entityManager.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        param.forEach((key, value) -> query.setParameter(key, value));
        query.setFirstResult((page - 1) * 15);
        query.setMaxResults(15);
        List<Map<String, Object>> result = query.getResultList();
        return result;
    }

}
